﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Reflection.Emit;

namespace Speedy
{
    public abstract class MasterTable:IDisposable
    {
        public List<Column> Columns { get; set; }
        public string TableName { get; set; }

        public MasterTable()
        {
            Columns = new List<Column>();
        }

        public void Save()
        {
            string where = string.Empty;
            string insert = string.Empty;
            string insert_values = string.Empty;
            string update = string.Empty;
            ParameterCollection prm = new ParameterCollection();

            foreach (Column col in this.Columns)
            {
                if (col.isKey == true)
                {
                    where += col.Name + "=@" + col.Name + " and ";
                    prm.AddItem(Prm.Get("@" + col.Name, col.Read()));
                }
                if (col.isKey == false && col.isIdentity == false)
                {
                    update += col.Name + "=@" + col.Name + ",";
                    prm.AddItem(Prm.Get("@" + col.Name, col.Read()));
                }
                if (col.isIdentity == false)
                {
                    insert += col.Name + ",";
                    insert_values += "@" + col.Name + ",";
                    prm.AddItem(Prm.Get("@" + col.Name, col.Read()));
                }
            }

            where = where.Remove(where.Length - 4, 4);
            if (update.Length > 0) update = update.Remove(update.Length - 1, 1);
            insert = insert.Remove(insert.Length - 1, 1);
            insert_values = insert_values.Remove(insert_values.Length - 1, 1);

            StringBuilder sb = new StringBuilder();
            if (!string.IsNullOrEmpty(update))
            {
                sb.Append(string.Format("if exists(select {0} from {1} where {2}) ", this.Columns[0].Name, this.TableName, where));
                sb.Append(string.Format("update {0} set {1} where {2}", this.TableName, update, where));
                sb.Append(string.Format("else insert into {0} ( {1} )  values ( {2} ) ", this.TableName, insert, insert_values));
            }
            else
            {
                sb.Append(string.Format("if not exists(select {0} from {1} where {2}) ", this.Columns[0].Name, this.TableName, where));
                sb.Append(string.Format("insert into {0} ( {1} )  values ( {2} ) ", this.TableName, insert, insert_values));
            }
            DataLayer.GetLayer.ExecQuery(sb.ToString(), prm.ToArray());
        }

        public virtual void Remove()
        {
            string where = string.Empty;
            ParameterCollection prm = new ParameterCollection();

            foreach (Column col in this.Columns)
            {
                if (col.isKey == true)
                {
                    where += col.Name + "=@" + col.Name + " and ";
                    prm.AddItem(Prm.Get("@" + col.Name, col.Read()));
                }
            }

            where = where.Remove(where.Length - 4, 4);

            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("delete from {0} where {1} ", this.TableName, where));

            DataLayer.GetLayer.ExecQuery(sb.ToString(), prm.ToArray());

        }

        public virtual DataTable AllData()
        {
            StringBuilder list = new StringBuilder();
            list.Append("select \r\n");

            foreach (Column col in this.Columns)
                if (!string.IsNullOrEmpty(col.Alias))
                    list.Append(col.Name + " as [" + col.Alias + "],");

            list.Remove(list.Length - 1, 1);
            list.Append(" from " + this.TableName);

            return DataLayer.GetLayer.FillData(list.ToString());
        }

        public List<MasterTable> ToList()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select \r\n");

            foreach (Column col in this.Columns)
                if (!string.IsNullOrEmpty(col.Alias))
                    sb.Append(col.Name + " as [" + col.Alias + "],");
                else
                    sb.Append(col.Name + ",");

            sb.Remove(sb.Length - 1, 1);
            sb.Append(" from " + this.TableName);

            Type tablo = this.GetType();
            ConstructorInfo constructor = tablo.GetConstructor(new Type[] { });

            List<MasterTable> liste = new List<MasterTable>();

            SqlDataReader rd = DataLayer.GetLayer.ReadData(sb.ToString());
            if (rd.HasRows)
                while (rd.Read())
                {
                    MasterTable createdObject = (MasterTable)constructor.Invoke(null);
                    for (int i = 0; i < rd.FieldCount; i++)
                        foreach (Column item in createdObject.Columns)
                            if (!(rd[i] == DBNull.Value)) item.SetValue(rd[i]); else item.SetValue(item.DefaultValue());
                    liste.Add(createdObject);
                }
            return liste;
        }

        public static DataTable List(MasterTable table)
        {
            string where = string.Empty;
            ParameterCollection prm = new ParameterCollection();

            foreach (Column col in table.Columns)
            {
                if (col.isKey == true)
                {
                    where += col.Name + "=@" + col.Name + " and ";
                    prm.AddItem(Prm.Get("@" + col.Name, col.Read()));
                }
            }

            where = where.Remove(where.Length - 3, 3);

            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("select * from {0} where {1} ", table.TableName, where));

            return DataLayer.GetLayer.FillData(sb.ToString(), prm.ToArray());
        }

        public void Find()
        {
            string where = string.Empty;
            string select = string.Empty;

            ParameterCollection prm = new ParameterCollection();
            foreach (Column col in this.Columns)
            {
                if (col.isKey == true)
                {
                    where += col.Name + "=@" + col.Name + " and ";
                    prm.AddItem(Prm.Get("@" + col.Name, col.Read()));
                }
                select += col.Name + ",";
            }

            where = where.Remove(where.Length - 4, 4);
            select = select.Remove(select.Length - 1, 1);

            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("select {0} from {1} where {2} ", select, this.TableName, where));

            SqlDataReader rd = DataLayer.GetLayer.ReadData(sb.ToString(), prm.ToArray());
            if (rd.HasRows)
                while (rd.Read())
                    for (int i = 0; i < rd.FieldCount; i++)
                        SetColumnValue(rd.GetName(i), rd[i]);
            else
            {
                foreach (Column item in this.Columns)
                    item.SetValue(item.DefaultValue());
            }
        }

        void SetColumnValue(string columnName, object value)
        {
            foreach (Column col in this.Columns)
                if (col.Name == columnName || col.Alias == columnName)
                {
                    if (value != DBNull.Value)
                        col.SetValue(value);
                    else
                        col.SetValue(col.DefaultValue());
                    break;
                }
        }



        public static void Save(params MasterTable[] tables)
        {
            string where = string.Empty;
            string insert = string.Empty;
            string insert_values = string.Empty;
            string update = string.Empty;
            ParameterCollection prm = new ParameterCollection();

            foreach (MasterTable table in tables)
            {
                foreach (Column col in table.Columns)
                {
                    if (col.isKey == true)
                    {
                        where += col.Name + "=@" + col.Name + " and ";
                        prm.AddItem(Prm.Get("@" + col.Name, col.Read()));
                    }
                    if (col.isKey == false && col.isNullable == false)
                    {
                        update += col + "=@" + col.Name + ",";
                        prm.AddItem(Prm.Get("@" + col.Name, col.Read()));
                    }
                    if (col.isNullable == false)
                    {
                        insert += col + ",";
                        insert_values += "@" + col.Name + ",";
                        prm.AddItem(Prm.Get("@" + col.Name, col.Read()));
                    }
                }

                where = where.Remove(where.Length - 3, 3);
                update = where.Remove(where.Length - 1, 1);
                insert = where.Remove(where.Length - 1, 1);
                insert_values = where.Remove(where.Length - 1, 1);

                StringBuilder sb = new StringBuilder();
                sb.Append(string.Format("if exits(select {0} from {1} where {2}) ", table.Columns[0].Name, table.TableName, where));
                sb.Append(string.Format("update {0} set {1} where {2}", table.TableName, update, where));
                sb.Append(string.Format("else insert into {0} ( {1} )  values ( {2} ) ", table.TableName, insert, insert_values));

                DataLayer.GetLayer.ExecQuery(sb.ToString(), prm.ToArray());
                prm.Clear();
            }
        }

        #region IDisposable Members

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        #endregion
    }

    public class Column : IComparer<Column>
    {
        int int_val;
        Int16 int16_val;
        decimal dec_val;
        string str_val;
        bool bool_val;
        DateTime date_val;
        byte byte_val;

        #region Set Value Method
        void SetValue(int value)
        {
            this.int_val = value;
        }
        void SetValue(decimal value)
        {
            this.dec_val = value;
        }
        void SetValue(DateTime value)
        {
            this.date_val = value;
        }
        void SetValue(string value)
        {
            this.str_val = value;
        }
        void SetValue(bool value)
        {
            this.bool_val = value;
        }
        void SetValue(Int16 value)
        {
            this.int16_val = value;
        }
        void SetValue(byte value)
        {
            this.byte_val = value;
        }
        public void SetValue(object value)
        {
            if (this.DataType.ToString().ToLower() == "system.string")
                this.SetValue(value.ToString());
            else
                if (value != null && !string.IsNullOrEmpty(value.ToString()))
                    switch (this.DataType.ToString().ToLower())
                    {
                        case "system.decimal":
                            if (DigitControl(value.ToString())) //sayı ise
                                this.SetValue(Convert.ToDecimal(value));
                            break;
                        case "system.ınt32":
                            if (DigitControl(value.ToString())) //sayı ise
                                this.SetValue(Convert.ToInt32(value));
                            break;
                        case "system.ınt16":
                            if (DigitControl(value.ToString())) //sayı ise
                                this.SetValue(Convert.ToInt16(value));
                            break;
                        case "system.datetime":
                            this.SetValue(Convert.ToDateTime(value));
                            break;
                        case "system.boolean":
                            if (DigitControl(value.ToString())) //sayı ise
                                this.SetValue(Convert.ToBoolean(value));
                            break;
                        case "system.byte":
                            if (DigitControl(value.ToString())) //sayı ise
                                this.SetValue(Convert.ToByte(value));
                            break;
                    }
        }
        #endregion

        bool DigitControl(string value)
        {
            foreach (char c in value.ToCharArray())
                if (!char.IsDigit(c))
                    return false;
            return true;
        }

        int ReadInt32()
        {
            return this.int_val;
        }
        decimal ReadDecimal()
        {
            return this.dec_val;
        }
        DateTime ReadDate()
        {
            return this.date_val;
        }
        string ReadString()
        {
            return this.str_val;
        }
        bool ReadBool()
        {
            return this.bool_val;
        }
        Int16 ReadInt16()
        {
            return this.int16_val;
        }
        byte ReadByte()
        {
            return this.byte_val;
        }
        public object Read()
        {
            switch (this.DataType.ToString().ToLower())
            {
                case "system.string":
                    return this.ReadString();
                case "system.decimal":
                    return this.ReadDecimal();
                case "system.ınt32":
                    return this.ReadInt32();
                case "system.ınt16":
                    return this.ReadInt16();
                case "system.datetime":
                    return this.ReadDate();
                case "system.boolean":
                    return this.ReadBool();
                case "system.byte":
                    return this.ReadByte();
                default:
                    return null;
            }
        }

        public Type DataType { get; set; }
        public string Name { get; set; }
        public Int16 Size { get; set; }
        public bool isNullable { get; set; }
        public bool isKey { get; set; }
        public string Alias { get; set; }
        public bool isIdentity { get; set; }
        public object DefaultValue()
        {
            switch (this.DataType.ToString().ToLower())
            {
                case "system.string":
                    return string.Empty;
                case "system.decimal":
                    return decimal.MinValue;
                case "system.int32":
                    return int.MinValue;
                case "system.int16":
                    return Int16.MinValue;
                case "system.datetime":
                    return DateTime.MinValue;
                case "system.boolean":
                    return false;
                default:
                    return null;
            }
        }

        public override string ToString()
        {
            if (string.IsNullOrEmpty(this.Alias))
                return this.Name;
            else
                return this.Alias;
        }

        public Column(Type dataType, string name, Int16 size)
        {
            this.DataType = dataType;
            this.Name = name;
            this.Size = size;
            this.isNullable = false;
            this.isKey = false;
            this.isIdentity = false;
        }
        public Column(Type dataType, string name)
        {
            this.DataType = dataType;
            this.Name = name;
            this.Size = 0;
            this.isNullable = false;
            this.isKey = false;
            this.isIdentity = false;
        }
        public Column(Type dataType, string name, bool isnullable)
        {
            this.DataType = dataType;
            this.Name = name;
            this.Size = 0;
            this.isNullable = isnullable;
            this.isKey = false;
            this.isIdentity = false;
        }
        public Column(Type dataType, string name, Int16 size, bool isnullable)
        {
            this.DataType = dataType;
            this.Name = name;
            this.Size = size;
            this.isNullable = isnullable;
            this.isKey = false;
            this.isIdentity = false;
        }
        public Column(Type dataType, string name, Int16 size, bool isnullable, bool iskey)
        {
            this.DataType = dataType;
            this.Name = name;
            this.Size = size;
            this.isNullable = isnullable;
            this.isKey = iskey;
            this.isIdentity = false;
        }
        public Column(Type dataType, string name, Int16 size, bool isnullable, bool iskey, bool isidentity)
        {
            this.DataType = dataType;
            this.Name = name;
            this.Size = size;
            this.isNullable = isnullable;
            this.isKey = iskey;
            this.isIdentity = isidentity;
        }

        #region IComparer<Column> Members

        public int Compare(Column x, Column y)
        {
            if (x.Name == y.Name)
                return 1;
            else
                return 0;
        }

        #endregion
    }
}